import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 直接导入广电用户数据到数据库的测试类
 * 这个类直接连接数据库并导入数据，无需通过Spring容器
 */
public class ImportGuangDianDataTest {
    
    private static final Logger logger = LoggerFactory.getLogger(ImportGuangDianDataTest.class);
    
    // 数据库连接信息
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String JDBC_USERNAME = "root";
    private static final String JDBC_PASSWORD = "password";
    
    // CSV文件路径
    private static final String CSV_FILE_PATH = "C:/Users/Administrator/RuoYi-Vue/guangdian-data-analysis/宽表数据.csv";
    
    // 批量插入大小
    private static final int BATCH_SIZE = 1000;
    
    public static void main(String[] args) {
        logger.info("开始直接导入广电用户数据到数据库...");
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            // 检查CSV文件是否存在
            File csvFile = new File(CSV_FILE_PATH);
            if (!csvFile.exists()) {
                logger.error("CSV文件不存在：{}", CSV_FILE_PATH);
                return;
            }
            
            logger.info("发现CSV文件：{}，文件大小：{}KB", CSV_FILE_PATH, csvFile.length() / 1024);
            
            // 加载JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 建立数据库连接
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
            conn.setAutoCommit(false);
            
            // 准备插入语句
            String sql = "INSERT INTO guang_dian_user " +
                         "(terminal_no, phone_no, sm_name, run_name, sm_code, owner_name, owner_code, " +
                         "run_time, address, estate_name, open_time, force_flag, is_heavy_user, " +
                         "tv_consumption_level, broadband_consumption_level, is_loyal_user, " +
                         "service_brand, tv_penetration, broadband_penetration, user_type, " +
                         "create_by, create_time) " +
                         "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            pstmt = conn.prepareStatement(sql);
            
            // 读取CSV文件并插入数据
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            
            try (BufferedReader reader = new BufferedReader(new FileReader(CSV_FILE_PATH))) {
                String line;
                boolean isFirstLine = true; // 跳过表头
                int importedCount = 0;
                int batchCount = 0;
                
                while ((line = reader.readLine()) != null) {
                    if (isFirstLine) {
                        isFirstLine = false;
                        continue;
                    }
                    
                    // 如果已经导入了2000条数据，停止导入
                    if (importedCount >= 2000) {
                        break;
                    }
                    
                    String[] fields = line.split(",");
                    if (fields.length >= 20) { // 确保有足够的字段
                        try {
                            // 设置参数
                            pstmt.setString(1, fields[0]);  // terminal_no
                            pstmt.setString(2, fields[1]);  // phone_no
                            pstmt.setString(3, fields[2]);  // sm_name
                            pstmt.setString(4, fields[3]);  // run_name
                            pstmt.setString(5, fields[4]);  // sm_code
                            pstmt.setString(6, fields[5]);  // owner_name
                            pstmt.setString(7, fields[6]);  // owner_code
                            
                            // 处理时间字段
                            if (fields.length > 7 && fields[7] != null && !fields[7].trim().isEmpty()) {
                                try {
                                    LocalDateTime runTime = LocalDateTime.parse(fields[7], formatter);
                                    pstmt.setTimestamp(8, java.sql.Timestamp.valueOf(runTime));
                                } catch (Exception e) {
                                    pstmt.setTimestamp(8, java.sql.Timestamp.valueOf(LocalDateTime.now()));
                                }
                            } else {
                                pstmt.setTimestamp(8, java.sql.Timestamp.valueOf(LocalDateTime.now()));
                            }
                            
                            pstmt.setString(9, fields.length > 8 ? fields[8] : "");   // address
                            pstmt.setString(10, fields.length > 9 ? fields[9] : "");  // estate_name
                            
                            if (fields.length > 10 && fields[10] != null && !fields[10].trim().isEmpty()) {
                                try {
                                    LocalDateTime openTime = LocalDateTime.parse(fields[10], dateFormatter).atStartOfDay();
                                    pstmt.setTimestamp(11, java.sql.Timestamp.valueOf(openTime));
                                } catch (Exception e) {
                                    pstmt.setTimestamp(11, java.sql.Timestamp.valueOf(LocalDateTime.now()));
                                }
                            } else {
                                pstmt.setTimestamp(11, java.sql.Timestamp.valueOf(LocalDateTime.now()));
                            }
                            
                            // 处理标签字段
                            pstmt.setString(12, fields.length > 11 ? fields[11] : "");  // force_flag
                            pstmt.setString(13, fields.length > 12 ? fields[12] : "");  // is_heavy_user
                            pstmt.setString(14, fields.length > 13 ? fields[13] : "");  // tv_consumption_level
                            pstmt.setString(15, fields.length > 14 ? fields[14] : "");  // broadband_consumption_level
                            pstmt.setString(16, fields.length > 15 ? fields[15] : "");  // is_loyal_user
                            pstmt.setString(17, fields.length > 16 ? fields[16] : "");  // service_brand
                            pstmt.setString(18, fields.length > 17 ? fields[17] : "");  // tv_penetration
                            pstmt.setString(19, fields.length > 18 ? fields[18] : "");  // broadband_penetration
                            pstmt.setString(20, fields.length > 19 ? fields[19] : "");  // user_type
                            
                            // 设置创建信息
                            pstmt.setString(21, "system");
                            pstmt.setTimestamp(22, java.sql.Timestamp.valueOf(LocalDateTime.now()));
                            
                            pstmt.addBatch();
                            batchCount++;
                            importedCount++;
                            
                            // 每BATCH_SIZE条数据执行一次批处理
                            if (batchCount >= BATCH_SIZE) {
                                pstmt.executeBatch();
                                conn.commit();
                                logger.info("已导入 {} 条数据", importedCount);
                                batchCount = 0;
                            }
                        } catch (Exception e) {
                            logger.error("处理第 {} 行数据时出错：{}", importedCount + 1, e.getMessage());
                            // 跳过当前行，继续处理下一行
                            continue;
                        }
                    }
                }
                
                // 执行剩余的批处理
                if (batchCount > 0) {
                    pstmt.executeBatch();
                    conn.commit();
                }
                
                logger.info("数据导入完成，成功导入 {} 条记录", importedCount);
                logger.info("仪表盘数据已准备就绪，可以查看统计信息");
            }
            
        } catch (ClassNotFoundException e) {
            logger.error("数据库驱动未找到：", e);
        } catch (SQLException e) {
            logger.error("数据库操作失败：", e);
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    logger.error("回滚事务失败：", ex);
                }
            }
        } catch (Exception e) {
            logger.error("数据导入过程中发生错误：", e);
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                logger.error("关闭资源失败：", e);
            }
            
            logger.info("数据导入任务完成");
        }
    }
}